"""
Case Type   : 二级分区
Case Name   : range_hash二级分区表:insert/explain/index/create as/create like
Create At   : 2022/2/24
Owner       : @daiguatutu
Description :
    1.创建二级分区表
    2.插入违反check约束的数据
    3.插入正确数据
    4.创建本地/唯一索引并查看索引
    5.查看执行计划
    6.create as创建表(普通表)
    7.create like(including all) from 分区表
    8.创建普通表
    9.创建表指定like including partition，使用partition by子句
    10.创建表指定like including partition
    11.创建表指定like
    12.查看分区信息
    13.清理环境
Expect      :
    1.成功
    2.合理报错
    3.成功
    4.成功
    5.成功
    6.成功
    7.合理报错
    8.成功
    9.合理报错
    10.合理报错
    11.成功
    12.成功
    13.清理成功
History     :
"""
import os
import re
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Subpartition(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_subpartition_0308'
        self.t_name_01 = 't_subpartition_0308_01'
        self.i_name_01 = 'i_subpartition_0308_01'
        self.i_name_02 = 'i_subpartition_0308_02'

    def test_subpartition(self):
        text = '-----step1:创建二级分区表 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (col_1 int primary key, " \
            f"col_2 int not null , col_3 varchar2 ( 30 ) not null , " \
            f"col_4 int generated always  as(2*col_2) stored , " \
            f"check (col_4 >= col_2))with(fillfactor=80) " \
            f"partition by range (col_1) subpartition by hash (col_2)" \
            f"( partition p_range_1 values less than( -10 )" \
            f"  ( subpartition p_hash_1_1 , subpartition p_hash_1_2 ," \
            f"    subpartition p_hash_1_3)," \
            f"  partition p_range_2 values less than( 20 )," \
            f"  partition p_range_3 values less than( 30)" \
            f"  ( subpartition p_hash_3_1 , subpartition p_hash_3_2 ," \
            f"    subpartition p_hash_3_3)," \
            f"    partition p_range_4 values less than( 50)" \
            f"  ( subpartition p_hash_4_1 , subpartition p_hash_4_2 ," \
            f"    subpartition t_subpartition_0350)," \
            f"  partition p_range_5 values less than( maxvalue )" \
            f") enable row movement;"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)

        text = '-----step2:插入违反check约束的数据 expect:合理报错-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values (-1,-1,'aa'),(-5,5,'bb');"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.violates_constraint_error_msg, msg,
                      '执行失败:' + text)

        text = '-----step3:插入正确数据 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values" \
            f"(1,1,'aa'),(5,5,'bb'),(11,2,'cc'),(19,8,'dd');"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, msg, '执行失败:' + text)

        text = '-----step4:创建本地/唯一索引并查看索引 expect:成功-----'
        self.log.info(text)
        sql = f"drop index if exists {self.i_name_01}; " \
            f"create index {self.i_name_01} " \
            f"on {self.t_name}(col_1,col_3) local; " \
            f"drop index if exists {self.i_name_02}; " \
            f"create unique index {self.i_name_02} on {self.t_name}(col_2);" \
            f"select relname from pg_partition " \
            f"where relname='p_hash_1_1_col_1_col_3_idx';" \
            f"\d+ {self.t_name} "
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.CREATE_INDEX_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertIn(f'"{self.i_name_02}" UNIQUE, btree (col_2)', msg,
                      '执行失败:' + text)
        self.assertIn(f'"{self.i_name_01}" btree (col_1, col_3) LOCAL', msg,
                      '执行失败:' + text)
        self.assertIn('p_hash_1_1_col_1_col_3_idx', msg, '执行失败:' + text)

        text = '-----step5:查看执行计划 expect:成功-----'
        self.log.info(text)
        sql = f"explain analyze select * from {self.t_name} " \
            f"partition(p_range_1) where col_1 >5; " \
            f"explain analyze select * from {self.t_name} " \
            f"subpartition(p_hash_1_3);"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn('Partitioned Seq Scan on', msg, '执行失败:' + text)

        text = '-----step6:create as创建表(普通表) expect:成功-----'
        self.log.info(text)
        sql = f"drop table if exists  {self.t_name_01};" \
            f"create table {self.t_name_01} as select * from {self.t_name};" \
            f"drop table if exists  {self.t_name_01};" \
            f"create table {self.t_name_01} " \
            f"as select col_1,col_2,col_3 from {self.t_name};"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)

        text = '-----step7:create like(including all) from 分区表 ' \
               'expect:合理报错-----'
        self.log.info(text)
        sql = f"drop table if exists  {self.t_name_01};" \
            f"create table {self.t_name_01} " \
            f"(like {self.t_name} including all );"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn('Create Table like with subpartition only support '
                      'range strategy.', msg, '执行失败:' + text)

        text = '-----step8:创建普通表; expect:成功-----'
        self.log.info(text)
        sql = f"drop table if exists {self.t_name_01};" \
            f"create table {self.t_name_01} (col_1 int primary key, " \
            f"col_2 int not null , col_3 varchar2 ( 30 ) not null , " \
            f"col_4 int generated always  as(2*col_2) stored , " \
            f"check (col_4 >= col_2));"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg,
                      '执行失败:' + text)

        text = '-----step9:创建表指定like including partition，使用partition by子句; ' \
               'expect:合理报错-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (like {self.t_name_01} " \
            f"including partition )with(fillfactor=80) " \
            f"partition by range (col_1) subpartition by hash (col_2)" \
            f"( partition p_range_1 values less than( -10 )" \
            f"  ( subpartition p_hash_1_1 , subpartition p_hash_1_2 ," \
            f"    subpartition p_hash_1_3)," \
            f"  partition p_range_2 values less than( 20 )," \
            f"  partition p_range_3 values less than( 30)" \
            f"  ( subpartition p_hash_3_1 , subpartition p_hash_3_2 ," \
            f"    subpartition p_hash_3_3)," \
            f"    partition p_range_4 values less than( 50)" \
            f"  ( subpartition p_hash_4_1 , subpartition p_hash_4_2 ," \
            f"    subpartition t_subpartition_0350)," \
            f"  partition p_range_5 values less than( maxvalue )" \
            f") enable row movement;"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIsNotNone(
            re.search(self.constant.unsupport_error_msg, msg, re.S),
            '执行失败:' + text)

        text = '-----step10:创建表指定like including partition; expect:合理报错-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (like {self.t_name_01} " \
            f"including partition );"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIn(' could not specify "INCLUDING PARTITION" '
                      'for non-partitioned-table', msg, '执行失败:' + text)

        text = '-----step11:创建表指定like; expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (like {self.t_name_01} " \
            f")with(fillfactor=80) " \
            f"partition by range (col_1) subpartition by hash (col_2)" \
            f"( partition p_range_1 values less than( -10 )" \
            f"  ( subpartition p_hash_1_1 , subpartition p_hash_1_2 ," \
            f"    subpartition p_hash_1_3)," \
            f"  partition p_range_2 values less than( 20 )," \
            f"  partition p_range_3 values less than( 30)" \
            f"  ( subpartition p_hash_3_1 , subpartition p_hash_3_2 ," \
            f"    subpartition p_hash_3_3)," \
            f"    partition p_range_4 values less than( 50)" \
            f"  ( subpartition p_hash_4_1 , subpartition p_hash_4_2 ," \
            f"    subpartition t_subpartition_0350)," \
            f"  partition p_range_5 values less than( maxvalue )" \
            f") enable row movement;"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg,
                      '执行失败:' + text)

        text = '-----step12:查看分区信息; expect:成功-----'
        self.log.info(text)
        select_sql = f"select relname, parttype, partstrategy, boundaries " \
            f"from pg_partition where partstrategy !='n' and parentid = " \
            f"(select oid from pg_class where relname = '{self.t_name}')" \
            f" order by relname;" \
            f"select a.relname,a.parttype,a.partstrategy,a.indisusable " \
            f"from pg_partition a,(select oid from pg_partition where " \
            f"parentid = (select oid from pg_class where " \
            f"relname = '{self.t_name}')) b where a.parentid = b.oid " \
            f"order by a.relname;"
        msg = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(msg)
        partition = ['p_range_1', 'p_range_2', 'p_range_3', 'p_range_4',
                     'p_range_5', 'p_hash_1_1', 'p_hash_1_2', 'p_hash_1_3',
                     'p_range_2_subpartdefault1', 'p_hash_3_1', 'p_hash_3_2',
                     'p_hash_3_3', 'p_range_5_subpartdefault1',
                     f'{self.t_name}']
        for par in partition:
            self.assertIn(par, msg, '执行失败:' + text)

    def tearDown(self):
        self.log.info('-----step13:清理环境-----')
        text_1 = '-----删除表 expect:成功-----'
        self.log.info(text_1)
        del_cmd = f'drop table if exists {self.t_name};' \
            f'drop table if exists {self.t_name_01};'
        msg = self.pri_sh.execut_db_sql(del_cmd)
        self.log.info(msg)

        self.assertEqual(2, msg.count(self.constant.TABLE_DROP_SUCCESS),
                         '执行失败:' + text_1)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
